use sqlx::{Executor, Pool, Sqlite};
use std::path::PathBuf;

use crate::models::{AppError, AppResult, SimpleConnection};

static mut POOL: Option<Pool<Sqlite>> = None;

#[inline]
fn get_db_path() -> PathBuf {
    let appdata = std::env!("APPDATA");
    let mut pathbuf = std::path::PathBuf::from(appdata);
    pathbuf = pathbuf.join("sqlgen-pong");
    if !pathbuf.exists() {
        let _ = std::fs::create_dir_all(&pathbuf);
    }
    pathbuf.join("data.db")
}

#[tauri::command]
pub async fn init() -> AppResult<bool> {
    let db_path = get_db_path();
    let options = sqlx::sqlite::SqliteConnectOptions::new()
        .create_if_missing(true)
        .filename(db_path);
    unsafe {
        POOL = Some(sqlx::sqlite::SqlitePoolOptions::new().connect_lazy_with(options));
        let mut conn = POOL.as_ref().unwrap().acquire().await?;
        let result = conn
            .fetch_optional(
                "SELECT name FROM sqlite_master WHERE type='table' and name='Connections'",
            )
            .await?;
        if result.is_none() {
            //创建表
            _ = conn.execute("CREATE TABLE Connections (Id text primary key, Name text, Host text, Port integer, UserName text, Password text,[Database] text,IsDeleted bool);").await?;
        }
    }
    Ok(true)
}

// Make a new connection
// Ensure [dotenvy] and [env_logger] have been setup
// pub async fn new<DB>() -> Result<DB::Connection>
// where
//     DB: Database,
// {
//     // setup_if_needed();

//     // Ok(DB::Connection::connect(&env::var("DATABASE_URL")?).await?)
// }
#[tauri::command]
pub async fn get_connections(keyword: Option<&str>) -> AppResult<Vec<crate::models::Connection>> {
    let mut conn = unsafe { POOL.as_ref().unwrap().acquire().await? };
    // sqlx::query(sql).bind(value)
    // sqlx::query!("sql",)
    let mut sql = String::from("SELECT * FROM Connections WHERE IsDeleted = 0");
    let mut parameters = vec![];
    if let Some(keyword) = keyword {
        sql.push_str(" AND ([Name] LIKE :keyword OR [Host] LIKE ?)");
        parameters.push(format!("%{}%", keyword));
    }
    let mut query = sqlx::query::<sqlx::Sqlite>(&sql);
    for item in parameters {
        query = query.bind(item);
    }
    let rows = query.fetch_all(&mut conn).await?;
    let mut result = vec![];
    for row in rows {
        result.push(crate::models::Connection::from_row(row));
    }
    for item in result.iter_mut() {
        item.password.clear()
    }
    Ok(result)
}

#[tauri::command]
pub async fn save_connection(data: crate::models::Connection) -> AppResult<()> {
    let mut conn = unsafe { POOL.as_ref().unwrap().acquire().await? };
    if data.id.is_none() {
        //插入数据库
        _ = sqlx::query("INSERT INTO Connections([Id],[Name],[Host],[Port],[UserName],[Password],[Database],[IsDeleted]) VALUES (?,?,?,?,?,?,?,?)")
            .bind(nanoid::nanoid!(10))
            .bind(data.name)
            .bind(data.host)
            .bind(data.port)
            .bind(data.username)
            .bind(data.password)
            .bind(data.database)
            .bind(false)
            .execute(&mut conn)
            .await?;
    } else {
        _ = sqlx::query("UPDATE [Connections] SET [Name]=?,[Host]=?,[Port]=?,[UserName]=?,Password=?,[Database]=? WHERE Id=?").bind(data.name).bind(data.host).bind(data.port).bind(data.username).bind(data.password).bind(data.database).bind(data.id).execute(&mut conn).await?;
    }
    Ok(())
}

#[tauri::command]
pub async fn get_connection(id: &str) -> AppResult<crate::models::Connection> {
    let mut conn = unsafe { POOL.as_ref().unwrap().acquire().await? };
    if let Some(row) = sqlx::query("SELECT * FROM Connections WHERE Id=? AND IsDeleted = 0")
        .bind(id)
        .fetch_optional(&mut conn)
        .await?
    {
        Ok(crate::models::Connection::from_row(row))
    } else {
        Err(AppError::new().message(&format!("Error on get connection info, id = {}", id)))
    }
}

#[tauri::command]
pub async fn delete_connection(id: &str) -> AppResult<()> {
    let mut conn = unsafe { POOL.as_ref().unwrap().acquire().await? };
    _ = sqlx::query("DELETE FROM Connections WHERE Id=? AND IsDeleted = 0")
        .bind(id)
        .execute(&mut conn)
        .await?;
    Ok(())
}

#[tauri::command]
pub async fn get_simple_connections() -> AppResult<Vec<SimpleConnection>> {
    let mut conn = unsafe { POOL.as_ref().unwrap().acquire().await? };
    let rows = sqlx::query::<sqlx::Sqlite>(
        "SELECT Id,Name,Host,Port FROM Connections WHERE IsDeleted = 0",
    )
    .fetch_all(&mut conn)
    .await?;
    let mut result = vec![];
    for row in rows {
        result.push(crate::models::SimpleConnection::from_row(row));
    }
    Ok(result)
}
